package com.database;

import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Iterator;
import java.util.List;
/**
 * <p>Title:SqlBatchExecute</p>
 * <p>Description: 执行SQ语句的统一接口，对数据库的sql语句操作的唯一类
 * 调用此类的示例如下：
 * String sql="update T_BB_BBXXY set zf_bj='1' where swjg_dm='1234'";
 * String sql2="update T_BB_BYS  set zf='1' where swjg_dm='564'";
 * ArrayList sqllist = new ArrayList();
 * sqllist.add(sql);
 * sqllist.add(sql1);
 * SqlBatchExecute bpo = new SqlBatchExecute(sqllist);
 * bpo.execute(conn);
 *
 * </p>
 * @author kevin
 * @Date 2009-3-12
 * @version 1.0
 */


public class SqlBatchExecute {

        private final static int MODE_LIST = 1;

        private final static int MODE_TEMPLET = 2;
        
        private final static int MODE_STEP_COMMIT=3;

        /**
         * 运行模式 是处于批量Sql模式还是sql模板模式
         */
        private int mode = 0;

        /**
         * 批量处理的sql集合 MODE_LIST时使用

         */
        private List<String> sqlList;

        /**
         * 批量处理的sql模板 MODE_TEMPLET时使用

         */
        private String sqlTemplet;

        /**
         * 批量处理的sql参数列表 MODE_TEMPLET时使用

         */
        private List<Object> sqlParms;

        /**
         * 转换SQLkey为SQL语句
         *
         * @param sqlKey sql key
         * @return sql语句
         */
//        public static String sqlKey2Sql(String sqlKey) {
//                return ApplicationContext.singleton().getValueAsString(
//                                ApplicationContext.BLH_FILE, sqlKey);
//        }

        /**
         * 判断执行结果是否成功
         * <p>此方法只在不需要关系具体执行结果时使用</p>
         * @param retArr 执行结果列表
         * @return 是否成功（全部成功返回true，任何一天失败返回false）

         */
        public static boolean judgeAllResult(int[] retArr){
                for (int i = 0;i <retArr.length;i++){
                        if (retArr[i] == Statement.EXECUTE_FAILED){
                                return false;
                        }
                }
                return true;
        }

        /**
         * 批量处理的sql集合
         * <p>
         * List的元素为String型的Sql语句
         * </p>
         *
         * @param sqlList
         *            Sql的列表

         */
        public SqlBatchExecute(List<String> sqlList) {
                this.mode = MODE_LIST;
                this.sqlList = sqlList;
        }
        public SqlBatchExecute(List<String> sqlList,int mode) {
        	if(mode==3){
            this.mode = MODE_STEP_COMMIT;
        	}else{
        	this.mode=MODE_LIST;
        	}
            this.sqlList = sqlList;
    }

        /**
         * 批量处理的sql模板
         * <p>
         * 参数sqlTemplet可以写为select * from tablename where a=?的形式

         * </p>
         * <p>
         * 参数sqlParms的每个元素必须为一个List元素，这个List元素中要按顺序设置Sql中的各个?的值

         * </p>
         *
         * @param sqlTemplet
         *            sql模板
         * @param params
         *            参数列表
         */
        public SqlBatchExecute(String sqlTemplet, List<Object> sqlParms) {
                this.mode = MODE_TEMPLET;
                this.sqlTemplet = sqlTemplet;
                this.sqlParms = sqlParms;
        }

        /**
         * 执行批量数据操作
         *
         * @param conn
         *            数据库连接

         * @return 每条操作的返回结果

         * @throws TaxBPOException
         *             BPO异常
         */
        public int[] execute(Connection conn)  {
                switch (mode) {
                case MODE_LIST: {
                        return executeList(conn);
                }
                case MODE_TEMPLET: {
                        return executeTemplet(conn);
                }
                case MODE_STEP_COMMIT:{
                	    return executeStepCommit(conn);
                }
                default: {
                        return null;
                    }
                }
        }
        //分部进行提交
        private int[] executeStepCommit(Connection conn){
        	int result[] = null;
            Statement stmt =null;
            try {
//                    if(conn==null){
//                        conn=DBConnection.getDSConnection();
//                    }
                    conn.setAutoCommit(false);
                    stmt= conn.createStatement();
                    int size = sqlList.size();
                    result = new int[size];
                    if (size > 0) {
                            for (int i = 0; i < size; i++) {
                                    String sqlStr = (String) sqlList.get(i);
//                                    System.out.println("增加第(" + i + ")个批量sql:" + sqlStr);
                                    stmt.addBatch(sqlStr);
                                    //System.out.println(sqlStr);
                            }
                            int[] rows = stmt.executeBatch();
                            int len = rows.length;
                            for (int j = 0; j < len; j++) {
                                    int rowCount = rows[j];
                                    if (rowCount <= 0) {
                                            System.err.println("第(" + j + ")个sql执行不成功");
                                            result[j] = rows[j];
                                    }
                            }
                            result=rows;
                    } else {
                            result = null;
//                            LogWritter.bizDetailInfo("批量执行时没有找到要执行的sql");
                    }
                    //conn.commit();
            } catch (SQLException ex) {
                result = null;
                    try {
                        conn.close();
                    } catch (SQLException ex1) {
                    }
                    ex.printStackTrace();
            }finally{
                try {
                    if(stmt!=null){
                        stmt.close();
                    }
//                    if(conn!=null){
//                        conn.close();
//                    }
                } catch (SQLException ex2) {
                }
            }
            return result;
        }

        /**
         * 按SqlList模式执行批量sql
         *
         * @param conn
         *            数据库连接

         * @return 每条操作的返回结果

         * @throws TaxBPOException
         *             BPO异常
         */
        private int[] executeList(Connection conn) {
                int result[] = null;
                Statement stmt =null;
                try {
//                        if(conn==null){
//                            conn=DBConnection.getDSConnection();
//                        }
                        conn.setAutoCommit(false);
                        stmt= conn.createStatement();
                        int size = sqlList.size();
                        result = new int[size];
                        if (size > 0) {
                                for (int i = 0; i < size; i++) {
                                        String sqlStr = (String) sqlList.get(i);
//                                        System.out.println("增加第(" + i + ")个批量sql:" + sqlStr);
                                        stmt.addBatch(sqlStr);
//                                        System.out.println(sqlStr);
                                }
                                int[] rows = stmt.executeBatch();
                                int len = rows.length;
                                for (int j = 0; j < len; j++) {
                                        int rowCount = rows[j];
                                        if (rowCount <= 0) {
//                                                LogWritter.bizDetailInfo("第(" + j + ")个sql执行不成功");
                                                result[j] = rows[j];
                                        }
                                }
                                result=rows;
                        } else {
                                result = null;
//                                LogWritter.bizDetailInfo("批量执行时没有找到要执行的sql");
                        }
                        conn.commit();
                } catch (SQLException ex) {
                    result = null;
                        try {
                        	conn.rollback();
                            conn.close();
                        } catch (SQLException ex1) {
                        }
                        ex.printStackTrace();
                }finally{
                    try {
                        if(stmt!=null){
                            stmt.close();
                        }
//                        if(conn!=null){
//                            conn.close();
//                        }
                    } catch (SQLException ex2) {
                    }
                }
                return result;
        }

        /**
         * 按sql模板方式执行批量sql
         *
         * @param conn
         * @return
         * @throws TaxBPOException
         */
        private int[] executeTemplet(Connection conn)  {
                int[] result = null;
                PreparedStatement prepareStatement = null;
                try {
                        prepareStatement = createPs(sqlParms, conn);

                        result = prepareStatement.executeBatch();
                } catch (SQLException ex) {
                        result = null;
                        ex.printStackTrace();
                } finally {
                    prepareStatement=null;
//                     if(prepareStatement!=null){
//                         try {
//                             prepareStatement.close();
//                         } catch (SQLException ex) {
//                             LogWritter.sysError(ex.getMessage(), ex);
//                             result = null;
//                             ex.printStackTrace();
//                         }
//                     }
                }
                return result;
        }

        /**
         * 根据设置的sql和sqlParms创建PreparedStatement
         *
         * 允许空的sqlParms但是不允许空的sql
         *
         * @param conn
         * @return PreparedStatement
         * @throws SQLException
         */
        private PreparedStatement createPs(List<Object> sqlParms, Connection conn)
                        throws SQLException {
                // 无SQL 返回null
                if (sqlTemplet == null) {
                }
                PreparedStatement ps = conn.prepareStatement(sqlTemplet);
                // 无参数 返回PreparedStatement
                if (sqlParms == null || sqlParms.size() == 0) {
                        ps.addBatch();
                        return ps;
                }

                // 递归设置PreparedStatement的参数

                for (Iterator<Object> paramListI = sqlParms.iterator(); paramListI.hasNext();) {
                        // 每个SQL的参数组
                        Object paramsObj = paramListI.next();
                        if (paramsObj == null) {
                                ps.addBatch();
                                continue;
                        }

                        Iterator<Object> sqlParmsI = ((List<Object>) paramsObj).iterator();
                        for (int j = 1; sqlParmsI.hasNext(); j++) {
                                // 每个SQL的参数

                                Object paramObj = sqlParmsI.next();
                                // 根据型别使用不同的数据

                                if (paramObj == null) {
                                        ps.setNull(j, Types.CHAR);
                                } else if (paramObj instanceof String) {
                                        ps.setString(j, (String) paramObj);
                                } else if (paramObj instanceof Long) {
                                        ps.setLong(j, ((Long) paramObj).longValue());
                                } else if (paramObj instanceof Integer) {
                                        ps.setInt(j, ((Integer) paramObj).intValue());
                                } else if (paramObj instanceof Double) {
                                        ps.setDouble(j, ((Double) paramObj).doubleValue());
                                } else if (paramObj instanceof Date) {
                                        ps.setDate(j, ((Date) paramObj));
                                } else if (paramObj instanceof Boolean) {
                                        ps.setBoolean(j, ((Boolean) paramObj).booleanValue());
                                } else if (paramObj instanceof Short) {
                                        ps.setShort(j, ((Short) paramObj).shortValue());
                                } else if (paramObj instanceof Time) {
                                        ps.setTime(j, ((Time) paramObj));
                                } else if (paramObj instanceof Timestamp) {
                                        ps.setTimestamp(j, ((Timestamp) paramObj));
                                } else if (paramObj instanceof Float) {
                                        ps.setFloat(j, ((Float) paramObj).floatValue());
                                } else if (paramObj instanceof Blob) {
                                        ps.setBlob(j, ((Blob) paramObj));
                                } else if (paramObj instanceof Clob) {
                                        ps.setClob(j, ((Clob) paramObj));
                                } else {
                                        ps.setObject(j, paramObj);
                                }
                        }
                        ps.addBatch();
                }

                return ps;
        }
}
